返回文章列表

PostgreSQL EXPLAIN、索引优化与执行计划实战

从 pg_stat_statements、EXPLAIN ANALYZE、Rows Removed by Filter、联合索引、表达式索引、部分索引、INCLUDE、统计信息和冗余索引治理出发，系统掌握 PostgreSQL 索引优化的证据链。

第 25 章：PostgreSQL EXPLAIN、索引优化与执行计划实战

本章统一使用 PostgreSQL 的 EXPLAIN。以下按 PostgreSQL 18 的执行计划格式讲解；PostgreSQL 14-17 的核心分析方法基本一致，但个别字段可能略有不同。(PostgreSQL)

0. 与前文的对应关系

本章偏实战案例，很多概念在前文已经系统讲过。阅读时可以按下面路径反查原理。

本章场景对应主讲章节
慢 SQL 入口与工作负载排序第 18 章：pg_stat_statements
EXPLAIN 字段、rows、loops、Buffers第 6 章：EXPLAIN 证据链
B-tree、联合索引、排序与 ORDER BY LIMIT第 4 章：判断索引是否真正生效
INCLUDE、覆盖索引、冗余索引与上线治理第 5 章：索引生命周期 Runbook
统计信息、估算偏差与 Extended Statistics第 7 章：估算偏差排障
GIN、GiST、BRIN、pg_trgm 与特殊索引选型第 8 章：特殊索引选型
Visibility Map 与 Index Only Scan 的 Heap Fetches第 12 章：FSM、Visibility Map 与 Index Only Scan

一、索引优化的核心思路

索引优化不是“看到慢 SQL 就加索引”，而是：

找到高成本 SQL → 看执行计划 → 判断数据访问量和估算是否准确 → 设计索引 → 再次验证 → 评估写入成本。

生产环境一般先通过 pg_stat_statements 找累计耗时高、平均耗时高或调用次数高的 SQL，再对具体 SQL 使用 EXPLAIN。pg_stat_statements 可以记录 SQL 的调用次数、规划时间和执行时间。(PostgreSQL)

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

二、面试常见的索引优化手段

优化手段典型场景原因与注意事项
单列 B-tree 索引等值、范围、关联条件B-tree 适合 =、>、<、BETWEEN 等条件，也能提供有序输出。
联合索引多个固定查询条件B-tree 联合索引通常应让常用等值条件处于前面，范围条件或排序列位于其后。前导列对扫描范围影响最大。(PostgreSQL)
排序索引ORDER BY ... LIMIT nB-tree 能直接按照索引顺序输出，避免显式 Sort，并可在取到前 n 行后停止扫描。(PostgreSQL)
部分索引只查询少量“热点状态”例如只索引 status='PENDING' 的订单，可以显著缩小索引并降低维护成本；查询条件必须能够推出索引谓词。(PostgreSQL)
表达式索引lower(email)、计算表达式普通 email 索引不能直接用于 lower(email)=...，需要建立相同表达式的索引。(PostgreSQL)
覆盖索引 INCLUDE查询只需要少数返回列可以产生 Index Only Scan，减少访问 Heap；但包含列越多，索引越大，写入成本越高。(PostgreSQL)
GIN/GiST/BRINJSONB、数组、全文、空间、时序大表GIN 常用于 JSONB、数组和全文；pg_trgm 可优化包含式 LIKE/ILIKE；BRIN 适合超大且物理顺序与时间等列高度相关的数据。(PostgreSQL)
更新统计信息执行计划估算严重错误ANALYZE 产生规划器统计信息；相关列可通过扩展统计信息改善联合选择率估算。(PostgreSQL)
删除冗余索引写多读少的表索引会增加磁盘占用以及 INSERT/UPDATE/DELETE 成本。主键和唯一约束已经自动创建唯一索引，不要重复创建。(PostgreSQL)
给外键引用端建立索引Join、父表删除或更新被引用端通常已有唯一索引，但引用端列是否有索引需要根据访问模式补充，否则父表删除、更新或关联查询可能扫描子表。(PostgreSQL)

联合索引顺序的实用规则

对于下面的查询：

WHERE tenant_id = 10
  AND status = 'PAID'
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC

通常可考虑：

CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);

思路是：

等值条件 → 等值条件 → 范围或排序条件

不过这不是机械定律，还要考虑：

  • 哪些查询最常执行；
  • 是否存在只按 tenant_id 查询；
  • 各列基数和数据倾斜；
  • 是否需要支持其他排序方向；
  • 索引大小和写入频率。

PostgreSQL 18 还能在特定数据分布下使用 B-tree skip scan，但通常只有前导列不同值较少时才有价值，不能代替合理的联合索引设计。(PostgreSQL)

三、先看懂 EXPLAIN

日常分析建议使用：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT ...;

当只关心实际行数、循环次数和 Buffer，想降低节点计时开销时，可以使用：

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT ...;

ANALYZE 会真正执行 SQL。对于修改语句，应当特别小心：

BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'CANCELLED'
WHERE id = 100;

ROLLBACK;

EXPLAIN ANALYZE 会实际执行 INSERT、UPDATE、DELETE 和 MERGE；事务中的 ROLLBACK 可以避免数据最终被修改。(PostgreSQL)

1. 计划基本字段

例如：

Index Scan using idx_orders_user_id on orders
  (cost=0.43..20.25 rows=5 width=64)
  (actual time=0.030..0.060 rows=8 loops=1)
字段含义
cost=0.43..20.25启动成本和完整执行成本，是规划器的抽象成本单位，不是毫秒
rows=5规划器估算该节点每次执行输出 5 行
width=64估算每行平均 64 字节
actual time=...实际节点启动时间和结束时间，单位毫秒
actual rows=8每次循环平均输出 8 行
loops=1这个节点执行了多少次

当 loops > 1 时，actual time 和 actual rows 是每次执行的平均值。估算节点的总工作量时，需要乘以 loops。成本值是抽象单位，上层节点的成本包含子节点成本。(PostgreSQL)

2. 最重要的执行计划信号

信号如何理解
Index Cond真正用于缩小索引扫描范围的条件
Filter候选数据取出后才执行的过滤条件
Rows Removed by Filter获取了多少无效候选行
Recheck CondBitmap、GiST、BRIN 等场景需要重新验证的条件
Seq Scan顺序扫描，不一定是问题
Index Scan读取索引后再访问表数据
Index Only Scan理论上只访问索引，但仍需观察 Heap Fetches
Bitmap Index Scan根据索引生成行位置位图
Bitmap Heap Scan根据位图按 Heap 页面获取数据
Sort Method排序算法，以及是否使用磁盘
Buffers: shared hit数据块已经在共享缓存中
Buffers: shared read数据块需要从文件系统读取
temp read/written排序、Hash 等操作溢出到临时文件

Rows Removed by Filter、loops 和估算行数偏差通常比“有没有走索引”更值得优先关注。BUFFERS 中的上层节点数据包含其子节点消耗；shared hit 表示避免了磁盘读取，但仍然存在内存访问和 CPU 成本。(PostgreSQL)

四、案例一：大量 Rows Removed by Filter

假设 orders 有 1000 万行：

SELECT *
FROM orders
WHERE user_id = 918273;

优化前的典型计划

Seq Scan on orders
  Filter: (user_id = 918273)
  Rows Removed by Filter: 9999980
  Buffers: shared read=120000

如何分析

实际只返回约 20 行，但扫描并过滤了接近 1000 万行：

扫描数据很多
返回数据很少
条件选择性较高

这通常说明缺少能够定位 user_id 的索引。

创建索引

CREATE INDEX idx_orders_user_id
ON orders (user_id);

ANALYZE orders;

优化后的典型计划

Index Scan using idx_orders_user_id on orders
  Index Cond: (user_id = 918273)
  Buffers: shared hit=20

关键变化：

Filter → Index Cond
Seq Scan → Index Scan
读取大量页面 → 只读取少数索引页和 Heap 页

但如果一个 user_id 对应了表中很大比例的数据，规划器仍可能选择 Seq Scan，这可能是正确选择。

生产环境在大表上创建索引时通常会评估 CREATE INDEX CONCURRENTLY，它不会阻塞正常写入，但需要更多扫描、时间和资源，而且失败时可能留下无效索引。(PostgreSQL)

五、案例二：联合索引同时解决过滤和排序

查询某个租户最近的已支付订单：

SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 50;

当前只有：

CREATE INDEX idx_orders_tenant ON orders (tenant_id);
CREATE INDEX idx_orders_status ON orders (status);

优化前的典型计划

Limit
  -> Sort
       Sort Key: created_at DESC
       Sort Method: top-N heapsort
       -> Bitmap Heap Scan on orders
            Recheck Cond: (tenant_id = 42)
            Filter: (status = 'PAID')
            Rows Removed by Filter: 80000
            -> Bitmap Index Scan on idx_orders_tenant
                 Index Cond: (tenant_id = 42)

或者可能看到：

BitmapAnd
  -> Bitmap Index Scan on idx_orders_tenant
  -> Bitmap Index Scan on idx_orders_status

问题

虽然使用了索引，但仍存在两个浪费：

  1. 先读取租户的全部订单，再过滤 status；
  2. 过滤后还要进行排序。

多个独立索引可以通过 BitmapAnd/BitmapOr 合并，但 Bitmap 扫描会丢失索引顺序，因此 ORDER BY 常常仍需要额外排序。(PostgreSQL)

优化索引

CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);

优化后的计划

Limit
  -> Index Scan using idx_orders_tenant_status_created on orders
       Index Cond:
         ((tenant_id = 42) AND (status = 'PAID'))

现在：

  • tenant_id 和 status 都用于索引定位；
  • created_at 已经有序；
  • 不再有 Sort；
  • 取到 50 行后可以立即停止扫描。

ORDER BY + LIMIT 是排序索引价值非常高的场景，因为没有合适索引时需要检查和排序大量候选行，而有序 B-tree 可以直接读取前若干行。(PostgreSQL)

六、案例三：函数导致普通索引无法使用

已有索引：

CREATE INDEX idx_users_email
ON users (email);

查询：

SELECT *
FROM users
WHERE lower(email) = '[email protected]';

优化前

Seq Scan on users
  Filter: (lower(email) = '[email protected]')
  Rows Removed by Filter: 1999999

普通索引保存的是 email，查询条件却是 lower(email)，二者不是同一个索引表达式。

方案一：表达式索引

CREATE INDEX idx_users_lower_email
ON users (lower(email));

优化后：

Index Scan using idx_users_lower_email on users
  Index Cond:
    (lower(email) = '[email protected]')

表达式索引要求查询中的表达式与索引表达式能够匹配。表达式索引本身也会增加插入和更新成本。(PostgreSQL)

方案二：将不可索引条件改写为范围

不建议：

WHERE date(created_at) = DATE '2026-06-01'

可以改写为：

WHERE created_at >= TIMESTAMP '2026-06-01 00:00:00'
  AND created_at <  TIMESTAMP '2026-06-02 00:00:00'

配合：

CREATE INDEX idx_orders_created_at
ON orders (created_at);

这样条件可以直接形成：

Index Cond:
  created_at >= ...
  AND created_at < ...

对于 timestamptz，边界应按照业务时区正确构造。

七、案例四：部分索引优化小比例热点数据

假设订单状态分布：

PAID       95%
CANCELLED   4%
PENDING     1%

系统频繁查询待处理订单：

SELECT id, user_id, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'PENDING'
ORDER BY created_at DESC
LIMIT 100;

如果建立完整索引：

CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);

索引会包含所有订单，但真正关注的只是 1% 的 PENDING 数据。

使用部分索引

CREATE INDEX idx_orders_pending_tenant_created
ON orders (tenant_id, created_at DESC)
WHERE status = 'PENDING';

典型计划

Limit
  -> Index Scan using idx_orders_pending_tenant_created on orders
       Index Cond: (tenant_id = 42)

计划里可能不会出现：

Filter: status = 'PENDING'

因为这个索引中的每一条记录都已经满足 status='PENDING'。

部分索引的关键限制是：规划阶段必须能证明查询条件蕴含索引谓词。下面这种参数化查询在通用执行计划中可能无法使用该索引：

PREPARE find_orders(text, int) AS
SELECT *
FROM orders
WHERE status = $1
  AND tenant_id = $2;

规划器无法假定 $1 永远等于 'PENDING'。部分索引适合条件固定、数据占比低且访问频繁的场景。(PostgreSQL)

八、案例五：用 INCLUDE 变成覆盖索引

查询：

SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 50;

已有索引：

CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);

执行计划可能是：

Index Scan using idx_orders_tenant_status_created on orders
  Index Cond:
    ((tenant_id = 42) AND (status = 'PAID'))
  Buffers: shared hit=180

虽然搜索和排序都很好，但返回 id、total_amount 时仍需要访问 Heap。

建立覆盖索引

CREATE INDEX idx_orders_tenant_status_created_cover
ON orders (tenant_id, status, created_at DESC)
INCLUDE (id, total_amount);

优化后的典型计划

Index Only Scan using idx_orders_tenant_status_created_cover on orders
  Index Cond:
    ((tenant_id = 42) AND (status = 'PAID'))
  Heap Fetches: 0

需要注意：

Index Only Scan ≠ 一定完全不访问 Heap

PostgreSQL 仍需根据 MVCC 可见性判断记录是否可见。可见性信息主要通过 Visibility Map 判断；如果页面最近发生过修改，可能仍需访问 Heap：

Heap Fetches: 8420

此时即使节点名称是 Index Only Scan，实际收益也可能不大。Visibility Map 位由 VACUUM 设置，数据修改会清除相应页面的可见性标记。(PostgreSQL)

因此覆盖索引更适合：

  • 读多写少；
  • 查询频繁；
  • 返回列较少；
  • 表能够正常执行 autovacuum；
  • 包含列不宽。

不要把大文本、JSONB 等宽列随意放入 INCLUDE，否则索引膨胀和写放大会很明显。

九、案例六：已经走索引，为什么还是慢

查询：

SELECT u.id, o.id
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.segment = 'VIP'
  AND o.status = 'PENDING';

已有索引：

CREATE INDEX idx_users_segment ON users (segment);
CREATE INDEX idx_orders_user_id ON orders (user_id);

典型执行计划

Nested Loop
  -> Index Scan using idx_users_segment on users u
       Index Cond: (segment = 'VIP')
       actual rows=5000 loops=1

  -> Index Scan using idx_orders_user_id on orders o
       Index Cond: (user_id = u.id)
       Filter: (status = 'PENDING')
       Rows Removed by Filter: 198
       actual rows=2 loops=5000

很多人看到 Index Scan 就认为计划已经很好，但这里的问题在于：

内层节点执行 5000 次
每次平均扫描约 200 条订单
绝大多数订单被 status 条件过滤

总候选数据量大约是：

5000 × (198 + 2) = 1,000,000 行

优化方案一：联合索引

CREATE INDEX idx_orders_user_status
ON orders (user_id, status);

优化后内层节点可能变为：

Index Scan using idx_orders_user_status on orders o
  Index Cond:
    ((user_id = u.id) AND (status = 'PENDING'))
  actual rows=2 loops=5000

优化方案二：部分索引

如果系统主要查询待处理订单：

CREATE INDEX idx_orders_pending_user
ON orders (user_id)
WHERE status = 'PENDING';

这通常比完整 (user_id, status) 索引更小。

这个案例最重要的知识点是：

一个很快的节点执行几万次，也可能成为整条 SQL 的主要成本。

actual rows 和 actual time 在 loops > 1 时是每次循环平均值，评估总工作量必须乘以 loops。(PostgreSQL)

十、案例七：执行计划估算错误，不应继续盲目加索引

计划中看到：

Bitmap Heap Scan on orders
  (cost=... rows=100 ...)
  (actual ... rows=800000 loops=1)

估算 100 行，实际 80 万行，误差 8000 倍。

这种误差可能导致规划器错误选择：

  • Nested Loop 而不是 Hash Join；
  • Index Scan 而不是 Seq Scan；
  • 错误的 Join 顺序；
  • 过小的 Hash 或 Sort 内存估算。

第一步：更新统计信息

ANALYZE orders;

第二步：提高倾斜列的统计目标

ALTER TABLE orders
ALTER COLUMN status SET STATISTICS 500;

ANALYZE orders;

第三步：为相关列建立扩展统计信息

例如 tenant_id 与 status 强相关：

CREATE STATISTICS st_orders_tenant_status
    (dependencies, mcv)
ON tenant_id, status
FROM orders;

ANALYZE orders;

PostgreSQL 默认主要使用单列统计信息，并通常假设多个条件相互独立。当列之间存在强相关性时，该假设会造成明显误差；扩展统计信息可以记录列之间的依赖关系或常见组合。(PostgreSQL)

所以应该先判断：

索引缺失？
还是统计信息不准？

十一、Seq Scan 不一定是坏计划

假设：

SELECT *
FROM orders
WHERE status = 'PAID';

而 PAID 占全表 95%。

即使存在：

CREATE INDEX idx_orders_status
ON orders (status);

规划器仍可能选择：

Seq Scan on orders
  Filter: (status = 'PAID')

这是合理的，因为查询本来就需要读取绝大多数行。使用普通 Index Scan 反而需要：

  1. 扫描大量索引项；
  2. 根据每个索引项访问 Heap；
  3. 执行大量随机页面访问。

顺序扫描可能更便宜。索引最适合减少数据访问量，而不是为了把节点名字从 Seq Scan 改成 Index Scan。普通索引扫描需要同时访问索引和 Heap；当命中行很多时，这种随机 Heap 访问可能较昂贵。(PostgreSQL)

十二、固定的执行计划分析流程

拿到一份计划后，建议严格按照下面顺序分析。

第一步：看总时间和 Buffer

Execution Time
Buffers: shared hit/read
temp read/written

确定瓶颈更偏向 CPU、数据读取还是临时文件。

第二步：从缩进最深的叶子节点向上读

执行计划是树：

扫描表或索引
    ↓
Join / Sort / Aggregate
    ↓
Limit / 最终输出

先看数据是怎么被读取的，再看上层如何处理这些数据。(PostgreSQL)

第三步：比较估算行数和实际行数

重点找：

rows=100
actual rows=100000

明显偏差通常意味着：

  • 统计信息过期；
  • 数据严重倾斜；
  • 多列相关；
  • 参数化计划不适合当前参数；
  • 条件表达式缺少统计信息。

第四步：区分 Index Cond 和 Filter

理想情况：

Index Cond:
  tenant_id = 42
  status = 'PENDING'

需要优化的情况：

Index Cond:
  tenant_id = 42

Filter:
  status = 'PENDING'

Rows Removed by Filter: 500000

这通常提示联合索引或部分索引的机会。

第五步：计算 rows × loops

特别关注 Nested Loop 内层节点：

actual rows=100 loops=50000

实际输出工作量约 500 万行。

第六步：检查排序和 Hash 是否落盘

Sort Method: external merge
Disk: 2048MB

或：

Hash
Batches: 32

说明操作可能发生磁盘溢出。优化方向可能是：

  • 用索引消除排序；
  • 提前减少输入行；
  • 检查 work_mem；
  • 修复行数估算；
  • 调整 Join 方式。

第七步：修改索引后重新验证

至少比较：

Execution Time
actual rows
Rows Removed by Filter
loops
Sort
Heap Fetches
shared read
temp written

不要只比较规划器的 cost。

十三、面试中可以这样回答

PostgreSQL 索引优化首先应从真实工作负载出发，通过 pg_stat_statements 找出高累计耗时或高平均耗时 SQL，再使用 EXPLAIN (ANALYZE, BUFFERS) 分析。重点比较估算行数和实际行数，观察 Index Cond、Filter、Rows Removed by Filter、loops、排序和 Buffer。 对高选择性等值或范围条件使用 B-tree；多条件查询按照等值列、范围或排序列设计联合索引；热点小集合使用部分索引；函数条件使用表达式索引或改写为可索引范围；读多写少的查询可用 INCLUDE 构建覆盖索引；JSONB、数组和全文使用 GIN，超大时序表可以考虑 BRIN。同时要更新统计信息、处理多列相关性，并避免重复索引和过度索引。最终必须重新执行计划验证实际耗时和数据块读取是否下降。